from PySide6.QtCore import Qt, QAbstractTableModel, QModelIndex, QSortFilterProxyModel
from PySide6.QtSql import QSqlQuery, QSqlRecord, QSqlField, QSqlQueryModel
from typing import Optional, Union, List, Any
from dbService.db_service import ConnectionManager, DBConfig, get_default_config


class EditableSqlModel(QAbstractTableModel):
    """
    通用可编辑 SQL 模型（支持排序、QSqlRecord、新增/删除）
    ------------------------------------------
    - 任意 SELECT SQL 查询
    - 数据加载到内存
    - 可在 QTableView 中直接编辑
    - 支持 record(row)、setRecord(row)
    - 支持 appendRecord()、removeRecord()
    - 支持 sort() 排序
    - 支持 toQueryModel() 输出为 QSqlQueryModel
    - 不自动保存数据库
    """

    def __init__(
        self,
        sql: str,
        params: Optional[Union[list, tuple]] = None,
        config: Optional[DBConfig] = None,
        parent=None
    ):
        super().__init__(parent)
        self.sql = sql
        self.params = params
        self.config = config
        self.headers: List[str] = []
        self.data_rows: List[List[Any]] = []
        self.sort_column: int = -1
        self.sort_order: Qt.SortOrder = Qt.AscendingOrder
        self.load_data()

    # =====================
    # 数据加载
    # =====================
    def load_data(self):
        if not self.config:
            config = get_default_config()
        else:
            config = self.config
        db = ConnectionManager.get_connection(config)
        query = QSqlQuery(db)
        if self.params:
            query.prepare(self.sql)
            for p in self.params:
                query.addBindValue(p)
            ok = query.exec()
        else:
            ok = query.exec(self.sql)

        if not ok:
            raise RuntimeError(f"SQL 执行失败: {query.lastError().text()}")

        record = query.record()
        self.headers = [record.fieldName(i) for i in range(record.count())]
        self.data_rows = []
        while query.next():
            row_rec = query.record()
            self.data_rows.append([row_rec.value(i) for i in range(row_rec.count())])

    # =====================
    # 基础接口
    # =====================
    def rowCount(self, parent=QModelIndex()):
        return len(self.data_rows)

    def columnCount(self, parent=QModelIndex()):
        return len(self.headers)

    def data(self, index, role=Qt.DisplayRole):
        if not index.isValid():
            return None
        value = self.data_rows[index.row()][index.column()]
        if role in (Qt.DisplayRole, Qt.EditRole):
            return value
        return None

    def headerData(self, section, orientation, role=Qt.DisplayRole):
        if role != Qt.DisplayRole:
            return None
        if orientation == Qt.Horizontal:
            return self.headers[section]
        return section + 1

    def flags(self, index):
        if not index.isValid():
            return Qt.ItemIsEnabled
        return Qt.ItemIsEnabled | Qt.ItemIsSelectable | Qt.ItemIsEditable

    def setData(self, index, value, role=Qt.EditRole):
        """
        重写 setData：仅当新值与旧值不同时，才更新数据并发出信号
        """
        # 校验基础条件
        if not index.isValid() or role != Qt.EditRole:
            return False

        row = index.row()
        col = index.column()
        old_value = self.data_rows[row][col]

        # 关键：判断值是否真的发生变化（处理 None 值比较）
        # 注意：None == None 为 True，但在某些场景下可能需要用 is 判断，这里保持兼容
        if old_value == value:
            return False  # 值未变化，直接返回 False，不发出信号

        # 值发生变化：更新数据并发出信号
        self.data_rows[row][col] = value
        self.dataChanged.emit(index, index, [Qt.DisplayRole, Qt.EditRole])
        return True

    # =====================
    # QSqlRecord 支持
    # =====================
    def record(self, row: int) -> Optional[QSqlRecord]:
        if row < 0 or row >= len(self.data_rows):
            return None
        rec = QSqlRecord()
        for col, name in enumerate(self.headers):
            field = QSqlField(name)
            field.setValue(self.data_rows[row][col])
            rec.append(field)
        return rec

    def setRecord(self, row: int, record: QSqlRecord):
        if row < 0 or row >= len(self.data_rows):
            return
        self.data_rows[row] = [record.value(i) for i in range(record.count())]
        self.dataChanged.emit(self.index(row, 0), self.index(row, len(self.headers) - 1))

    # =====================
    # 新增 / 删除
    # =====================
    def appendRecord(self, record: Optional[QSqlRecord] = None):
        new_row = [None] * len(self.headers)
        if record is not None:
            new_row = [record.value(i) for i in range(record.count())]
        pos = len(self.data_rows)
        self.beginInsertRows(QModelIndex(), pos, pos)
        self.data_rows.append(new_row)
        self.endInsertRows()

    def removeRecord(self, row: int):
        if 0 <= row < len(self.data_rows):
            self.beginRemoveRows(QModelIndex(), row, row)
            del self.data_rows[row]
            self.endRemoveRows()

    # =====================
    # 排序功能
    # =====================
    def sort(self, column: int, order: Qt.SortOrder = Qt.AscendingOrder):
        """在内存中对列排序"""
        if column < 0 or column >= len(self.headers):
            return
        self.layoutAboutToBeChanged.emit()
        self.data_rows.sort(key=lambda r: (r[column] is None, r[column]), reverse=(order == Qt.DescendingOrder))
        self.sort_column = column
        self.sort_order = order
        self.layoutChanged.emit()

    # =====================
    # 导出辅助
    # =====================
    def get_all_data(self):
        return self.data_rows

    def get_row_dicts(self, key_lower: bool = True) -> List[dict]:
        """
        返回字典列表（支持控制键是否小写）

        Args:
            key_lower: True-键转为小写，False-保留原始键名（默认 True）
        """
        headers = [header.lower() for header in self.headers] if key_lower else self.headers
        return [dict(zip(headers, row)) for row in self.data_rows]

    # =====================
    # 转为 QSqlQueryModel
    # =====================
    def toQueryModel(self) -> QSqlQueryModel:
        temp_model = QSqlQueryModel()
        db = ConnectionManager.get_connection(self.config)
        create_sql = "CREATE TEMP TABLE tmp_export ({});".format(
            ", ".join([f'"{h}" TEXT' for h in self.headers])
        )
        insert_sql = "INSERT INTO tmp_export VALUES ({});".format(
            ", ".join(["?"] * len(self.headers))
        )

        q = QSqlQuery(db)
        q.exec("DROP TABLE IF EXISTS tmp_export;")
        q.exec(create_sql)
        q.prepare(insert_sql)

        for row in self.data_rows:
            for val in row:
                q.addBindValue(val)
            q.exec()
            q.clear()

        temp_model.setQuery("SELECT * FROM tmp_export;", db)
        return temp_model
